package com.yenlien.traceyun.plant.dao;

import com.alibaba.fastjson.JSONObject;
import com.yenlien.traceyun.plant.dock_device.kebai.KeBaiData;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

/**
 * @author jialong
 * @date 2021/04/02
 * @description: 科百数据层
 **/

public interface KeBaiDao extends BaseJpaDao<KeBaiData> {


    @Query(value = "SELECT senval paramVal, thattime cTime FROM t_kebai WHERE date_format(thattime, '%Y-%m') = ?1 AND sensorname = ?2 AND company_id = ?3", nativeQuery = true)
    List<JSONObject> getLine(String yearMonth, String paramName, String fType);



    @Query(value = "SELECT ANY_VALUE(id) id, ANY_VALUE(thattime) ctime, sensorname paramName, ANY_VALUE(unit) paramUnit, ANY_VALUE(senval) paramVal, " +
            "ANY_VALUE(company_id) companyId, ANY_VALUE(equipment_id) equipmentId, ANY_VALUE(sensortype) type, ANY_VALUE(base_soil_id) baseSoilId, " +
            "ANY_VALUE(equipment_name) equipmentName FROM" +
            "(SELECT * FROM t_kebai WHERE equipment_id = ?1 HAVING 1 ORDER BY thattime DESC) t GROUP BY t.sensorname", nativeQuery = true)
    List<JSONObject> getLast(String devId);



    @Query(value = "SELECT id, thattime ctime, sensorname paramName, unit paramUnit, senval paramVal, " +
            "company_id companyId, equipment_id equipmentId, sensortype type, base_soil_id baseSoilId, equipment_name equipmentName FROM" +
            " t_kebai WHERE equipment_id = ?1  ORDER BY create_time DESC LIMIT ?2", nativeQuery = true)
    List<JSONObject> getLast(String equId, Integer limit);


//    @Query(value = "SELECT sensorname paramName FROM t_kebai WHERE nodecode = ?1 GROUP BY sensorname", nativeQuery = true)
    @Query(value = "SELECT sensorname paramName, ANY_VALUE(unit) paramUnit FROM t_kebai WHERE equipment_id = ?1 GROUP BY sensorname", nativeQuery = true)
    List<JSONObject> getParamNameList(String equId);


    @Query(value = "SELECT GROUP_CONCAT(CONCAT(sensorname, '=', senval)) FROM t_kebai WHERE create_time IS NOT NULL AND equipment_id = ?1 GROUP BY create_time", nativeQuery = true)
    List<String> getByCreateTime(String equId);

    @Query(value = "SELECT GROUP_CONCAT(CONCAT(sensorname, '=', senval)) FROM t_kebai WHERE create_time IS NOT NULL AND equipment_id = ?1 AND create_time BETWEEN ?2 AND ?3 GROUP BY create_time", nativeQuery = true)
    List<String> getByCreateTime(String equId, String startTime, String endTime);

    @Query(value = "SELECT senval paramVal, thattime `time` FROM t_kebai WHERE equipment_id = ?1 AND sensorname = ?2", nativeQuery = true)
    List<JSONObject> getByParamName(String equId, String paramName);


    @Query(value = "SELECT senval paramVal, thattime `time` FROM t_kebai WHERE equipment_id = ?1 AND sensorname = ?2 AND thattime BETWEEN ?3 AND ?4", nativeQuery = true)
    List<JSONObject> getByParamName(String equId, String paramName, String startTime, String endTime);


    /**
     * 根据地块ID查询数据
     *
     * @param baseSoilId
     * @return
     */
    List<KeBaiData> findByBaseSoilId(String baseSoilId);


    @Query(value = "SELECT * FROM t_kebai WHERE equipment_id = ?1 AND thattime BETWEEN ?2 AND ?3 ORDER BY thattime DESC", nativeQuery = true)
    List<KeBaiData> getHistoryData(String devId, String startTime, String endTime);

    @Query(value = "SELECT * FROM t_kebai WHERE equipment_id = ?1 AND sensorname = ?2 AND thattime BETWEEN ?3 AND ?4 ORDER BY thattime DESC", nativeQuery = true)
    List<KeBaiData> getHistoryData(String devId, String paramName, String startTime, String endTime);

    @Query(value = "SELECT * FROM t_kebai WHERE equipment_id = ?1  ORDER BY thattime DESC", nativeQuery = true)
    List<KeBaiData> getHistoryData(String devId);


    @Query(value = "SELECT sensorname paramName, MAX(senval) max, MIN(senval) min, ROUND(AVG(senval), 2) avg FROM t_kebai WHERE date_format(thattime, '%Y-%m') = ?1 AND company_id = ?2 GROUP BY sensorname ", nativeQuery = true)
    List<JSONObject> getAnalysis(String yearMonth, String fType);

    @Query(value = "SELECT sensorname paramName, MAX(senval) max, MIN(senval) min, ROUND(AVG(senval), 2) avg FROM t_kebai " +
            "WHERE date_format(thattime, '%Y-%m') = ?1 AND company_id = ?2 AND equipment_id = ?3 GROUP BY sensorname ", nativeQuery = true)
    List<JSONObject> getAnalysis(String yearMonth, String fType, String equId);

    @Query(value = "SELECT sensorname paramName, MAX(senval) max, MIN(senval) min, ROUND(AVG(senval), 2) avg FROM t_kebai " +
            "WHERE date_format(thattime, '%Y-%m') = ?1 AND equipment_id = ?2 AND sensorname = ?3 GROUP BY sensorname ", nativeQuery = true)
    JSONObject getAnalysisName(String yearMonth, String equId, String paramName);



    @Query(value = "SELECT sensorname paramName, MAX(senval) max, MIN(senval) min, ROUND(AVG(senval), 2) avg FROM t_kebai " +
            "WHERE date_format(thattime, '%Y-%m-%d') = ?1 AND company_id = ?2 AND equipment_id = ?3 GROUP BY sensorname ", nativeQuery = true)
    List<JSONObject> getAnalysisDay(String yearMonth, String fType, String equId);

    @Query(value = "SELECT senval paramVal, sensorname paramName, thattime cTime, equipment_id devId FROM t_kebai WHERE date_format(thattime, '%Y-%m') = ?1 AND sensorname = ?2 AND equipment_id = ?3", nativeQuery = true)
    List<JSONObject> getGraphAnalyse(String yearMonth, String paramName, String equId);


    @Query(value = "SELECT senval paramVal, sensorname paramName, thattime cTime, equipment_id devId, unit FROM t_kebai WHERE date_format(thattime, '%Y-%m') = ?1 AND equipment_id = ?2 ORDER BY thattime", nativeQuery = true)
    List<JSONObject> getGraphAnalyse(String yearMonth, String equId);


    /**
     * 根据设备ID查询历史数据
     *
     * @param equipmentId
     * @return
     */
    @Query(value = "SELECT * FROM t_kebai WHERE equipment_id = ?1 ORDER BY create_time", nativeQuery = true)
    List<KeBaiData> findByEquipmentId(String equipmentId);

    @Query(value = "SELECT * FROM t_kebai WHERE equipment_id = ?1 AND create_time BETWEEN ?2 AND ?3 ORDER BY create_time", nativeQuery = true)
    List<KeBaiData> findHistoryData(String equId, Date startTime, Date endTime);


    /**
     * 根据点的ID获取该点绑定设备3天前至现在的数据,每天各一条
     *
     * @param baseSoilId
     * @return
     */
    @Query(value = "SELECT ANY_VALUE(id) id, ANY_VALUE(equipment_id) equipment_id, ANY_VALUE(nodecode) nodecode, ANY_VALUE(base_soil_id) base_soil_id, " +
            "ANY_VALUE(company_id) company_id, ANY_VALUE(equipment_name) equipment_name, ANY_VALUE(nodename) nodename, ANY_VALUE(unit) unit, ANY_VALUE(senval) senval, " +
            "ANY_VALUE(sensortype) sensortype, ANY_VALUE(sensorname) sensorname, ANY_VALUE(sensorcode) sensorcode, ANY_VALUE(thattime) thattime, ANY_VALUE(stationname) stationname, " +
            "ANY_VALUE(stationcode) stationcode, ANY_VALUE(create_time) create_time FROM t_kebai WHERE base_soil_id = ?1 " +
            "AND TO_DAYS(NOW()) = TO_DAYS(thattime) GROUP BY sensorname" +
            " UNION " +
            "SELECT ANY_VALUE(id) id, ANY_VALUE(equipment_id) equipment_id, ANY_VALUE(nodecode) nodecode, ANY_VALUE(base_soil_id) base_soil_id, " +
            "ANY_VALUE(company_id) company_id, ANY_VALUE(equipment_name) equipment_name, ANY_VALUE(nodename) nodename, ANY_VALUE(unit) unit, ANY_VALUE(senval) senval, " +
            "ANY_VALUE(sensortype) sensortype, ANY_VALUE(sensorname) sensorname, ANY_VALUE(sensorcode) sensorcode, ANY_VALUE(thattime) thattime, ANY_VALUE(stationname) stationname, " +
            "ANY_VALUE(stationcode) stationcode, ANY_VALUE(create_time) create_time FROM t_kebai WHERE base_soil_id = ?1 " +
            "AND TO_DAYS(DATE_SUB(NOW(), INTERVAL 1 DAY)) = TO_DAYS(thattime) GROUP BY sensorname" +
            " UNION " +
            "SELECT ANY_VALUE(id) id, ANY_VALUE(equipment_id) equipment_id, ANY_VALUE(nodecode) nodecode, ANY_VALUE(base_soil_id) base_soil_id, " +
            "ANY_VALUE(company_id) company_id, ANY_VALUE(equipment_name) equipment_name, ANY_VALUE(nodename) nodename, ANY_VALUE(unit) unit, ANY_VALUE(senval) senval, " +
            "ANY_VALUE(sensortype) sensortype, ANY_VALUE(sensorname) sensorname, ANY_VALUE(sensorcode) sensorcode, ANY_VALUE(thattime) thattime, ANY_VALUE(stationname) stationname, " +
            "ANY_VALUE(stationcode) stationcode, ANY_VALUE(create_time) create_time FROM t_kebai WHERE base_soil_id = ?1 " +
            "AND TO_DAYS(DATE_SUB(NOW(), INTERVAL 2 DAY)) = TO_DAYS(thattime) GROUP BY sensorname",
            nativeQuery = true)
    List<KeBaiData> findList3Days(String baseSoilId);

}
